package com.pur.zsms.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.common.vo.CommonType;
import com.pur.zsms.dao.IDistributorDao;
import com.pur.zsms.documentsmanage.dao.IDomDao;
import com.pur.zsms.documentsmanage.po.DomPo;
import com.pur.zsms.foodmanage.food.dao.IFoodDao;
import com.pur.zsms.foodmanage.food.po.FoodMaterPo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.foodmanage.food.vo.FoodMaterVo;
import com.pur.zsms.foodmanage.food.vo.FoodVo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.matermanage.mater.vo.OrderDistributorMaterRealVo;
import com.pur.zsms.ordercustommanage.po.OrderDistributorMaterRealPo;
import com.pur.zsms.ordercustommanage.po.OrderDistributorPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierPo;
import com.pur.zsms.ordercustommanage.vo.OrderDistributorVo;
import com.pur.zsms.ordercustommanage.vo.OrderSupplierVo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.role.vo.RoleVo;
import com.pur.zsms.pmsnmanage.user.dao.IUserDao;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;

@Repository("distributorDao")
public class DistributorDaoImpl extends GenericDao implements IDistributorDao {
	public PaginationSupport listMaterForDistributor(PageController pageController, MaterVo materVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT DISTINCT f.mater_id materId, f.mater_name materName, f.mater_type materType, f.unit, f.specifications, f.brands, ( SELECT s.mater_number FROM t_store_material s WHERE s.mater_id = f.mater_id AND store_house_id = 73),h.mater_level  FROM t_material f,t_material_level h WHERE 1=1  AND f.mater_id = h.`mater_id` AND h.`cust_id`=0 AND h.`delete_flag` =0 ");
		if (materVo != null) {
			if (StringUtils.isNotBlank(materVo.getMaterName())) {
				ql.append(" and f.mater_name like '%" + materVo.getMaterName().trim() + "%'");
			}
			if (materVo.getMaterType() != 0) {
				ql.append(" and f.mater_type = '" + materVo.getMaterType() + "'");
			}

			if (materVo.getMaterId() != 0) {
				ql.append(" and f.mater_Id = '" + materVo.getMaterId() + "'");
			}
		}
		ql.append(" order by f.mater_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<Object[]> getOrderSupplierMaterForDistributor(int materId, int materLevel, String date, int supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT o.ord_mater_id, m.mater_name, m.mater_type, m.unit, m.brands, m.specifications, o.ord_mater_weight, (SELECT s2.code FROM t_supplier_shop s2 WHERE s2.sup_id = o.`ord_sup_id` AND s2.shop_id = s.`ord_cust_id`),o.ord_mater_level  FROM t_order_supplier_mater o, t_order_supplier s , t_material m WHERE o.ord_sup_id = s.ord_sup_id AND o.ord_id = s.ord_id AND s.ord_date = '"
				+ date
				+ "' AND o.ord_sup_id='"
				+ supId
				+ "' AND o.ord_mater_id ='"
				+ materId
				+ "' AND o.ord_mater_level ='"
				+ materLevel
				+ "' AND m.mater_id = o.ord_mater_id ");
		List<Object[]> list = this.entityManager.createNativeQuery(sql.toString()).getResultList();

		return list;
	}

	public void addOrderDistributorPo(OrderDistributorPo c) throws GenericException {
		this.persist(c);
	}

	public void addOrderDistributorMaterRealPo(OrderDistributorMaterRealPo c) throws GenericException {
		this.persist(c);
	}

	public void updateOrderDistributorMaterRealPo(OrderDistributorMaterRealPo c) throws GenericException {
		this.merge(c);
	}

	public void updateOrderDistributorPo(OrderDistributorPo c) throws GenericException {
		this.merge(c);
	}

	public void deleteOrderMaterForDistributor(OrderDistributorMaterRealPo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete OrderDistributorMaterRealPo o where o.ordId=" + c.getOrdId() + " and o.ordMaterId = '" + c.getOrdMaterId()
				+ "' and o.ordMaterLevel = '" + c.getOrdMaterLevel() + "'");
		if (c.getOrdDistributorMaterId() != 0) {
			ql.append(" and o.ordDistributorMaterId = '" + c.getOrdDistributorMaterId() + "'");
		}
		template.executeUpdate(ql.toString());
	}

	public void deleteOrderForDistributor(OrderDistributorMaterRealVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete OrderDistributorMaterRealPo o where o.ordId='" + c.getOrdId() + "'");
		template.executeUpdate(ql.toString());

		StringBuffer ql2 = new StringBuffer();
		ql2.append(" delete OrderDistributorPo o where o.ordId='" + c.getOrdId() + "'");

		template.executeUpdate(ql2.toString());
	}

	@Override
	public PaginationSupport listDistributorOrder(PageController pageController, OrderDistributorMaterRealVo orderDistributorMaterRealVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT d.ord_id, d.ord_sup_id, (SELECT s.sup_companyname FROM t_supplier s WHERE s.sup_id = d.ord_sup_id) AS supname, d.ord_state_distributor, d.ord_date, d.ord_day, d.ord_value,d.ord_describe ,d.`ord_distributor_id`,(SELECT s.sup_companyname  FROM t_supplier s  WHERE s.sup_id = d.ord_distributor_id) AS distributorname FROM t_order_distributor d WHERE 1=1  ");

		if (orderDistributorMaterRealVo.getOrdDistributorId() == 0 && orderDistributorMaterRealVo.getOrdSupId() == 0) {
			ql.append(" and  1=2 ");
		}
		if (orderDistributorMaterRealVo.getOrdDistributorId() != 0) {
			ql.append(" and  d.ord_distributor_id='" + orderDistributorMaterRealVo.getOrdDistributorId() + "'");
		}

		if (orderDistributorMaterRealVo.getOrdId() != 0) {
			ql.append(" and d.ord_id  = '");
			ql.append(orderDistributorMaterRealVo.getOrdId());
			ql.append("'");
		}

		if (orderDistributorMaterRealVo.getOrderDate() != null && !orderDistributorMaterRealVo.getOrderDate().equals("")) {
			ql.append(" and d.ord_date  = '");
			ql.append(orderDistributorMaterRealVo.getOrderDate());
			ql.append("'");
		}
		if (orderDistributorMaterRealVo.getOrdStateDistributor() != 0) {
			ql.append(" and d.ord_state_distributor  = '");
			ql.append(orderDistributorMaterRealVo.getOrdStateDistributor());
			ql.append("'");
		}

		if (orderDistributorMaterRealVo.getOrdSupId() != 0) {
			ql.append(" and d.ord_sup_id  = '");
			ql.append(orderDistributorMaterRealVo.getOrdSupId());
			ql.append("'");
		}

		ql.append(" ORDER BY  d.ord_date desc");

		return this.findByNativeQuery(ql.toString(), pageController);

	}

	public List<OrderDistributorPo> getOrderDistributorPo(OrderDistributorVo orderDistributorVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderDistributorPo o where 1=1 ");
		if (orderDistributorVo.getOrdId() != 0) {
			ql.append(" and  o.ordId = '" + orderDistributorVo.getOrdId() + "'");
		}
		if (orderDistributorVo.getOrdDistributorId() != 0) {
			ql.append(" and  o.ordDistributorId = '" + orderDistributorVo.getOrdDistributorId() + "'");
		}
		if (orderDistributorVo.getOrdSupId() != 0) {
			ql.append(" and  o.ordSupId = '" + orderDistributorVo.getOrdSupId() + "'");
		}
		List<OrderDistributorPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderDistributorMaterRealPo> getOrderDistributorMaterRealPo2(OrderDistributorMaterRealVo tOrderDistributorMaterRealVo)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o1 ");
		ql.append(" from OrderDistributorPo o,OrderDistributorMaterRealPo o1 where  1=1 and o.ordId = o1.ordId ");
		if (StringUtils.isNotEmpty(tOrderDistributorMaterRealVo.getOrdDate())) {
			ql.append(" and  o.ordDate = '" + tOrderDistributorMaterRealVo.getOrdDate() + "'");
		}
		if (tOrderDistributorMaterRealVo.getOrdDistributorId() != 0) {
			ql.append(" and  o.ordDistributorId = '" + tOrderDistributorMaterRealVo.getOrdDistributorId() + "'");
		}
		if (tOrderDistributorMaterRealVo.getOrdSupId() != 0) {
			ql.append(" and  o.ordSupId = '" + tOrderDistributorMaterRealVo.getOrdSupId() + "'");
		}
		if (tOrderDistributorMaterRealVo.getOrdMaterId() != 0) {
			ql.append(" and  o1.ordMaterId = '" + tOrderDistributorMaterRealVo.getOrdMaterId() + "'");
		}
		List<OrderDistributorMaterRealPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<OrderDistributorMaterRealPo> getOrderDistributorMaterRealPoForLastMaterValue(OrderDistributorMaterRealVo tOrderDistributorMaterRealVo)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select a ");
		ql.append(" from OrderDistributorPo b,OrderDistributorMaterRealPo a where  1=1 and a.ordId = b.ordId AND b.ordSupId = '"
				+ tOrderDistributorMaterRealVo.getOrdSupId() + "' AND a.ordMaterId = '" + tOrderDistributorMaterRealVo.getOrdMaterId()
				+ "' AND b.ordStateDistributor = '" + CommonType.ORD_STATE_DISTRIBUTOR_2 + "' AND a.ordMaterValueUnit != '0' AND a.ordMaterValueUnit  IS NOT NULL  ORDER BY b.ordDate DESC");
		List<OrderDistributorMaterRealPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public PaginationSupport findDistributorDetailOrder(PageController pageController, OrderDistributorMaterRealVo orderDistributorMaterRealVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT dr.ord_id, dr.ord_distributor_mater_id, dr.code, dr.ord_mater_id, m.mater_name, m.mater_type, m.brands, m.specifications, m.unit, dr.ord_mater_level, dr.ord_mater_value_unit, dr.ord_mater_value, dr.ord_mater_weight, dr.ord_mater_weight_reality, dr.ord_sup_id, (SELECT s.sup_companyname FROM t_supplier s WHERE s.sup_id = d.ord_sup_id) AS supname,dr.ord_mater_value_last FROM t_order_distributor d, t_order_distributor_mater_reality dr , t_material m WHERE 1 = 1 AND m.mater_id = dr.ord_mater_id AND d.ord_id = dr.ord_id AND d.ord_sup_id = dr.ord_sup_id AND d.ord_distributor_id = '"
				+ orderDistributorMaterRealVo.getOrdDistributorId()
				+ "' AND d.ord_sup_id = '"
				+ orderDistributorMaterRealVo.getOrdSupId()
				+ "' AND d.ord_id = '" + orderDistributorMaterRealVo.getOrdId() + "' ORDER BY m.mater_type,dr.ord_mater_id  ");

		return this.findByNativeQuery(ql.toString(), pageController);

	}

	@Override
	public PaginationSupport findDistributorDetailOrderForSup(PageController pageController, OrderDistributorMaterRealVo orderDistributorMaterRealVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT dr.ord_id, dr.ord_distributor_mater_id, dr.code, dr.ord_mater_id, m.mater_name, m.mater_type, m.brands, m.specifications, m.unit, dr.ord_mater_level, dr.ord_mater_value_unit, dr.ord_mater_value, dr.ord_mater_weight, dr.ord_mater_weight_reality, dr.ord_sup_id, (SELECT s.sup_companyname FROM t_supplier s WHERE s.sup_id = d.ord_sup_id) AS supname,dr.ord_mater_value_last FROM t_order_distributor d, t_order_distributor_mater_reality dr , t_material m WHERE 1 = 1 AND m.mater_id = dr.ord_mater_id AND d.ord_id = dr.ord_id AND d.ord_sup_id = dr.ord_sup_id AND d.ord_sup_id = '"
				+ orderDistributorMaterRealVo.getOrdSupId()
				+ "' AND d.ord_id = '"
				+ orderDistributorMaterRealVo.getOrdId()
				+ "' ORDER BY m.mater_type,dr.ord_mater_id  ");

		return this.findByNativeQuery(ql.toString(), pageController);

	}

	public List<OrderDistributorMaterRealPo> getOrderDistributorMaterRealPo(OrderDistributorMaterRealPo orderDistributorMaterRealPo)
			throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from OrderDistributorMaterRealPo o where 1=1 ");

		if (orderDistributorMaterRealPo.getOrdDistributorMaterId() != 0) {
			ql.append(" and  o.ordDistributorMaterId = '" + orderDistributorMaterRealPo.getOrdDistributorMaterId() + "'");
		}
		if (orderDistributorMaterRealPo.getOrdId() != 0) {
			ql.append(" and  o.ordId = '" + orderDistributorMaterRealPo.getOrdId() + "'");
		}
		if (orderDistributorMaterRealPo.getOrdSupId() != 0) {
			ql.append(" and  o.ordSupId = '" + orderDistributorMaterRealPo.getOrdSupId() + "'");
		}
		if (orderDistributorMaterRealPo.getOrdMaterId() != 0) {
			ql.append(" and  o.ordMaterId = '" + orderDistributorMaterRealPo.getOrdMaterId() + "'");
		}
		if (orderDistributorMaterRealPo.getOrdMaterLevel() != 0) {
			ql.append(" and  o.ordMaterLevel = '" + orderDistributorMaterRealPo.getOrdMaterLevel() + "'");
		}
		List<OrderDistributorMaterRealPo> list = template.find(ql.toString(), null);
		return list;
	}

	/**
	 * 计算总价格
	 * */
	public List<Object> getOrderDistributorMaterValueAll(int ordId) throws GenericException {

		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT SUM(dr.ord_mater_value) FROM t_order_distributor_mater_reality dr, t_order_distributor d WHERE 1 = 1 AND d.ord_id = dr.ord_id AND d.ord_id= '"
				+ ordId + "'");

		List<Object> list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		return list;
	}
}
